﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESZZBusiness
    {
        #region 判断进料计划是否已全部配送，如果是，则修改进料计划状态为2
        /// <summary>
        /// 判断进料计划是否已全部配送，如果是，则修改进料计划状态为2
        /// </summary>
        /// <param name="strPlanID"></param>
        /// <returns></returns>
        public void UpdatePlanStatus(string strPlanID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT pd.id,pd.planid,pd.requireqty,pd.qty");
            strQuery.AppendLine("FROM zzjlplandetail pd");
            strQuery.AppendLine("WHERE 1 = 1");
            strQuery.AppendFormat("AND pd.planid = '{0}'", strPlanID);
            strQuery.AppendLine("AND pd.requireqty > pd.qty");

            DataTable DT = OracleHelper.GetDataTable(strQuery.ToString());

            if (DT.Rows.Count > 0)
            {
                //
            }
            else
            {
                StringBuilder strSql = new StringBuilder();
                strSql.AppendLine(string.Format("UPDATE zzjlplan SET status = 2 WHERE ID = '{0}'", strPlanID));
                OracleHelper.ExecuteSql(strSql.ToString());
            }

        }
        #endregion

        #region 进料明细报表
        #region 分页查询
        public uMESPagingDataDTO GetSourceData_D(string[] whereQuery, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(whereQuery);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel_D(string[] whereQuery)
        {
            string strSQL = GetSQL_D(whereQuery);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(string[] whereQuery)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT f.factoryname,t.teamname,ws.workstationname,pf.productfamilyname,psd.processno,");
            strQuery.AppendLine("       pb.productname,p.productrevision,p.description,d.qty,psd.createdate");
            strQuery.AppendLine("FROM zzpsddetail d");
            strQuery.AppendLine("LEFT JOIN zzpsd psd ON psd.id = d.psdid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = d.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN productfamily pf ON pf.productfamilyid = psd.productfamilyid");
            strQuery.AppendLine("LEFT JOIN factory f ON f.factoryid = d.factoryid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = d.teamid");
            strQuery.AppendLine("LEFT JOIN workstation ws ON ws.workstationid = d.workstationid");
            strQuery.AppendLine("WHERE 1 = 1");

            if (!string.IsNullOrEmpty(whereQuery[0])) //车型
            {
                strQuery.AppendLine(string.Format("AND psd.productfamilyid = '{0}'", whereQuery[0]));
            }
            if (!string.IsNullOrEmpty(whereQuery[1])) //责任单位
            {
                strQuery.AppendLine(string.Format("AND psd.factoryid = '{0}'", whereQuery[1]));
            }
            if (!string.IsNullOrEmpty(whereQuery[2])) //班组
            {
                strQuery.AppendLine(string.Format("AND psd.teamid = '{0}'", whereQuery[2]));
            }
            if (!string.IsNullOrEmpty(whereQuery[3])) //配送地点
            {
                strQuery.AppendLine(string.Format("AND d.workstationid = '{0}'", whereQuery[3]));
            }
            if (!string.IsNullOrEmpty(whereQuery[4])) //令号
            {
                strQuery.AppendLine(string.Format("AND LOWER(psd.processno) LIKE '%{0}%'", whereQuery[4]));
            }
            if (!string.IsNullOrEmpty(whereQuery[5])) //开始时间
            {
                strQuery.AppendLine(string.Format("AND psd.createdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", whereQuery[5]));
            }
            if (!string.IsNullOrEmpty(whereQuery[6])) //结束时间
            {
                strQuery.AppendLine(string.Format("AND psd.createdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", whereQuery[6]));
            }

            strQuery.AppendLine("ORDER BY psd.createdate DESC");

            return strQuery.ToString();
        }
        #endregion
        #endregion

        #region 进料信息统计报表
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(string[] whereQuery, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL(whereQuery);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(string[] whereQuery)
        {
            string strSQL = GetSQL(whereQuery);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL(string[] whereQuery)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT * FROM (");
            strQuery.AppendLine("SELECT pf.productfamilyname,f.factoryname,t.teamname,ws.workstationname,p.processno,");
            strQuery.AppendLine("   pb.productname,pr.description,d.sequence,");
            strQuery.AppendLine("   SUM(d.requireqty) AS requireqty,SUM(d.qty) AS qty,(SUM(d.requireqty) - SUM(d.qty)) AS qjqty");
            strQuery.AppendLine("FROM zzjlplandetail d");
            strQuery.AppendLine("LEFT JOIN zzjlplan p ON p.id = d.planid");
            strQuery.AppendLine("LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid");
            strQuery.AppendLine("LEFT JOIN factory f ON f.factoryid = d.factoryid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = d.teamid");
            strQuery.AppendLine("LEFT JOIN workstation ws ON ws.workstationid = d.workstationid");
            strQuery.AppendLine("LEFT JOIN product pr ON pr.productid = d.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = pr.productbaseid");
            strQuery.AppendLine("WHERE 1 = 1 AND p.billtype = 'JLJH'");
            strQuery.AppendFormat("AND p.plandate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", DateTime.Now.ToString("yyyy-MM-dd"));

            if (!string.IsNullOrEmpty(whereQuery[0])) //车型
            {
                strQuery.AppendLine(string.Format("AND p.productfamilyid = '{0}'", whereQuery[0]));
            }
            if (!string.IsNullOrEmpty(whereQuery[1])) //责任单位
            {
                strQuery.AppendLine(string.Format("AND d.factoryid = '{0}'", whereQuery[1]));
            }
            if (!string.IsNullOrEmpty(whereQuery[2])) //班组
            {
                strQuery.AppendLine(string.Format("AND d.teamid = '{0}'", whereQuery[2]));
            }
            if (!string.IsNullOrEmpty(whereQuery[3])) //配送地点
            {
                strQuery.AppendLine(string.Format("AND d.workstationid = '{0}'", whereQuery[3]));
            }
            if (!string.IsNullOrEmpty(whereQuery[4])) //令号
            {
                strQuery.AppendLine(string.Format("AND LOWER(p.processno) LIKE '%{0}%'", whereQuery[4]));
            }

            strQuery.AppendLine("GROUP BY pf.productfamilyname,f.factoryname,t.teamname,ws.workstationname,p.processno,d.sequence,pb.productname,pr.description");
            strQuery.AppendLine(")");
            strQuery.AppendLine("WHERE qjqty > 0");
            strQuery.AppendLine("ORDER BY factoryname,teamname,workstationname,productfamilyname,processno,productname");

            return strQuery.ToString();
        }
        #endregion
        #endregion

        #region 获取进料计划物料列表
        public DataTable GetMaterial(string strPage, string strType, string strPlanID, string strFactoryID, string strTeamID, string strStationID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT p.productfamilyid,pf.productfamilyname,pd.factoryid,pd.teamid,p.qty,p.processno,pd.id,p.parentid,pd.parentid AS dparentid,");
            strQuery.AppendLine("       p.plandate,p.planno,pd.workstationid,pd.sequence,pd.workflowstr,t.teamname,pd.productid,");
            strQuery.AppendLine("       pb.productname,pr.description,(NVL(pd.requireqty,0) - NVL(pd.qty,0)) AS qtyrequired,");
            strQuery.AppendLine("       pd.requireqty AS rqty,pd.qty AS pqty,f.factoryname,ws.workstationname,DECODE(pd.status,0,'未配送',1,'已配送') AS dstatus");
            strQuery.AppendLine("FROM zzjlplandetail pd");
            strQuery.AppendLine("LEFT JOIN zzjlplan p ON p.id = pd.planid");
            strQuery.AppendLine("LEFT JOIN factory f ON f.factoryid = pd.factoryid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = pd.teamid");
            strQuery.AppendLine("LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid");
            strQuery.AppendLine("LEFT JOIN workstation ws ON ws.workstationid = pd.workstationid");
            strQuery.AppendLine("LEFT JOIN product pr ON pr.productid = pd.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = pr.productbaseid");
            strQuery.AppendLine("WHERE 1 = 1");
            strQuery.AppendLine(string.Format("AND p.id = '{0}'", strPlanID));

            if (strPage == "MaterialPlan")
            {
                //
            }
            else if (strPage == "MaterialSignIn")
            {
                strQuery.AppendLine("AND (NVL(pd.requireqty,0) - NVL(pd.qty,0)) > 0");

                if (strType == "JLJH")
                {
                    strQuery.AppendLine("AND pd.status = 0");
                }
            }

            if (strFactoryID != "")
            {
                strQuery.AppendLine(string.Format("AND pd.factoryid = '{0}'", strFactoryID));
            }

            if (strTeamID != "")
            {
                strQuery.AppendLine(string.Format("AND pd.teamid = '{0}'", strTeamID));
            }

            if (strStationID != "")
            {
                strQuery.AppendLine(string.Format("AND pd.workstationid = '{0}'", strStationID));
            }

            strQuery.AppendLine("ORDER BY f.factoryname,t.teamname,pd.sequence,ws.workstationname");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取BOM物料列表
        public DataTable GetMaterial(string strBOMID, int intQty, string strFactoryID, string strTeamID, string strStationID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT NVL(pb1.productname,pb2.productname) AS productname,");
            strQuery.AppendLine("NVL(p1.description,p2.description) AS DESCRIPTION,");
            strQuery.AppendLine("NVL(p1.productid,p2.productid) AS productid,");
            strQuery.AppendLine("t.teamname,t.teamid,f.factoryname,f.factoryid,ws.workstationname,ws.workstationid,");
            strQuery.AppendLine(string.Format("pmli.workflowstr,pmli.sequenceno AS sequence,(pmli.qtyrequired * {0}) AS qtyrequired,", intQty));
            strQuery.AppendLine(string.Format("(pmli.qtyrequired * {0}) AS rqty,'0' AS pqty", intQty));
            strQuery.AppendLine("FROM productmateriallistitem pmli");
            strQuery.AppendLine("LEFT JOIN product p1 ON p1.productid = pmli.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb1 ON pb1.productbaseid = p1.productbaseid");
            strQuery.AppendLine("LEFT JOIN productbase pb2 ON pb2.productbaseid = pmli.productbaseid");
            strQuery.AppendLine("LEFT JOIN product p2 ON p2.productid = pb2.revofrcdid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = pmli.teamid");
            strQuery.AppendLine("LEFT JOIN factory f ON f.factoryid = pmli.factoryid");
            strQuery.AppendLine("LEFT JOIN workstation ws ON ws.workstationid = pmli.workstationid");
            strQuery.AppendLine(string.Format("WHERE pmli.bomid = '{0}'", strBOMID));

            if (strFactoryID != "")
            {
                strQuery.AppendLine(string.Format("AND pmli.factoryid = '{0}'", strFactoryID));
            }

            if (strTeamID != "")
            {
                strQuery.AppendLine(string.Format("AND pmli.teamid = '{0}'", strTeamID));
            }

            if (strStationID != "")
            {
                strQuery.AppendLine(string.Format("AND pmli.workstationid = '{0}'", strStationID));
            }

            strQuery.AppendLine("ORDER BY f.factoryname,t.teamname,pmli.sequenceno,ws.workstationname");


            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 责任单位
        public DataTable GetFactory()
        {
            string strSql = "SELECT factoryid,factoryname,description FROM factory";
            return OracleHelper.GetDataTable(strSql);
        }
        #endregion

        #region 车型
        public DataTable GetFamily()
        {
            string strSql = "SELECT productfamilyid,productfamilyname,description,bomid,productfamilyid || ':' || bomid AS familyid FROM productfamily";
            return OracleHelper.GetDataTable(strSql);
        }
        #endregion

        #region 班组
        public DataTable GetTeam()
        {
            string strSql = "SELECT teamid,teamname,description FROM team";
            return OracleHelper.GetDataTable(strSql);
        }
        #endregion

        #region 配送地点
        public DataTable GetStation()
        {
            string strSql = "SELECT workstationid,workstationname,description FROM workstation";
            return OracleHelper.GetDataTable(strSql);
        }
        #endregion
    }
}
